using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace DailyTransaction
{
    class DAO_Rcpt
    {
        private string connectionString;
        private SqlConnection con;  
        private bool connect()
        {

            connectionString = GlobalClass.getConnectionString();
            if (GlobalClass.errmsg != "")
            {
                MessageBox.Show(GlobalClass.errmsg);
                return false;
            }
            try
            {
                if (con == null)
                {
                    con = new SqlConnection(connectionString);
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                }
                else if (con.State == ConnectionState.Closed)
                    con.Open();
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            return true;
            
        }
        public void disconnect()
        {
            try
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
            catch (SqlException se)
            { }
            finally {
                con.Close();
            }
        }

        public int executeNonquery(string sql)
        {
            int rslt = 0;
            connect();
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Connection = con;
            rslt = cmd.ExecuteNonQuery();
            disconnect();
            return rslt;
        }

        public DataTable executeQuery(string sql)
        {
            if (connect() == false)
                return null;
            SqlDataAdapter adap = new SqlDataAdapter(sql, con);
            DataTable tbl = new DataTable();
            adap.Fill(tbl);
            disconnect();
            return tbl;
        }

        internal DataTable get_newID()
        {
            string sql = "Select * from Receptionist"
                       + " order by receptID";
            DataTable tbl = executeQuery(sql);
            return tbl;            
        }

        internal int insert_new_receptionist(string id, string name)
        {
            string sql = "Insert into Receptionist values('" + id + "', '" + name + "')";
            return executeNonquery(sql);
        }

        public DataTable get_lst_Receptionist()
        {
            string sql = "Select recept_name from Receptionist"
                        + " UNION Select ' '";
            DataTable tbl = executeQuery(sql);
            return tbl;
        }

        internal int insert_new_company(string pCompcode, string pCompName, string pCompSUNC, 
                                    string pKindPmnt, string pMed, string pServ, string pT0, 
                                    string pT4, string pDisp, string pDrug, string pCreateD, 
                                    bool pAda, bool pSama)
        {
            int num = 0;
            if (connect() == false)
                return 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Insert_Company";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@comp_code", pCompcode);
            cmd.Parameters.AddWithValue("@KIND_PMENT", pKindPmnt);
            cmd.Parameters.AddWithValue("@COMP_SUNCD", pCompSUNC);
            cmd.Parameters.AddWithValue("@COMP_NAME",pCompName);
            cmd.Parameters.AddWithValue("@COMP_CREAT",pCreateD);
            cmd.Parameters.AddWithValue("@ACC_CRE_MED",pMed);
            cmd.Parameters.AddWithValue("@ACC_CRE_SERV",pServ);
            cmd.Parameters.AddWithValue("@ACC_CRE_DRUG",pDrug);
            cmd.Parameters.AddWithValue("@ACC_CRE_DISP",pDisp);
            cmd.Parameters.AddWithValue("@T0_code",pT0);
            cmd.Parameters.AddWithValue("@T4_code",pT4);
            cmd.Parameters.AddWithValue("@ADA",pAda);
            cmd.Parameters.AddWithValue("@SAMA", pSama);
            cmd.Parameters["@COMP_CREAT"].DbType = DbType.DateTime;
            cmd.Parameters["@ADA"].DbType = DbType.Byte;
            cmd.Parameters["@SAMA"].DbType = DbType.Byte;
            cmd.Connection = con;
            try
            {
                num = cmd.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                MessageBox.Show("Error: " + se.Message);
            }
            finally
            {
                disconnect();
            }
            return num;
        }

        internal int Update_Kind_Pmnt_Desc()
        {
            if (connect() == false)
                return -1;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Update_KIND_PMENT_DESC";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            int num = 0;
            try
            {
                num = cmd.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                MessageBox.Show("Error: " + se.Message);
                num = 0;
            }
            finally {
                disconnect();
            }
            return num;
        }

        internal int update_data(string pCompcode, string pCompName, string pCompSUNC,
                                    string pKindPmnt, string pMed, string pServ, string pT0,
                                    string pT4, string pDisp, string pDrug, string pCreateD,
                                    bool pAda, bool pSama)
        {
            int num = 0;
            if (connect() == false)
                return 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_Update_Company";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@comp_code", pCompcode);
            cmd.Parameters.AddWithValue("@KIND_PMENT", pKindPmnt);
            cmd.Parameters.AddWithValue("@COMP_SUNCD", pCompSUNC);
            cmd.Parameters.AddWithValue("@COMP_NAME", pCompName);
            cmd.Parameters.AddWithValue("@COMP_CREAT", pCreateD);
            cmd.Parameters.AddWithValue("@ACC_CRE_MED", pMed);
            cmd.Parameters.AddWithValue("@ACC_CRE_SERV", pServ);
            cmd.Parameters.AddWithValue("@ACC_CRE_DRUG", pDrug);
            cmd.Parameters.AddWithValue("@ACC_CRE_DISP", pDisp);
            cmd.Parameters.AddWithValue("@T0_code", pT0);
            cmd.Parameters.AddWithValue("@T4_code", pT4);
            cmd.Parameters.AddWithValue("@ADA", pAda);
            cmd.Parameters.AddWithValue("@SAMA", pSama);
            cmd.Parameters["@COMP_CREAT"].DbType = DbType.DateTime;
            cmd.Parameters["@ADA"].DbType = DbType.Byte;
            cmd.Parameters["@SAMA"].DbType = DbType.Byte;
            cmd.Connection = con;
            try
            {
                num = cmd.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                MessageBox.Show("Error: " + se.Message);
            }
            finally
            {
                disconnect();
            }
            return num;
        }
    }
}
